package soar.test.jpa.mysql;

import java.util.Date;
import java.util.List;
import java.util.Random;
import java.util.stream.Collectors;
import java.io.InputStream;
import java.math.BigInteger;
import java.time.Clock;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

import javax.persistence.EntityManager;
import javax.persistence.LockModeType;
import javax.persistence.NoResultException;
import javax.persistence.TemporalType;

import org.hibernate.exception.ConstraintViolationException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import soar.test.jpa.share.Resource;
import soar.test.jpa.share.entity.mysql.TestJob;
import soar.test.jpa.share.entity.mysql.TestLog;
import soar.test.jpa.share.entity.mysql.TestStat;
import soar.test.jpa.share.entity.mysql.TestStatId;
import soar.test.jpa.share.entity.mysql.TestUser;

public class App
{
    private final static Logger logger = LoggerFactory.getLogger(App.class);

    public static void main(String args[])
    {
        new App().run();
        System.exit(0);
    }

    private static final String PERSISTENCE_UNIT = "mysql";

    private void run()
    {
        try
        {
            init_database();

            // 测试数据库连接超时重连
            // EntityManager em = Resource.getInstance()
            // .getMySQLEntityManagerHandler().createEntityManager();
            // em.close();
            // Thread.sleep(30 * 1000);

            String split = "=============================================================";
            logger.info(split);

            create();
            logger.info(split);

            add();
            logger.info(split);

            update();
            logger.info(split);

            query();
            logger.info(split);

            remove();
            logger.info(split);

            composite_primary_key();
            logger.info(split);

            multi_table();
            logger.info(split);

            close_database();
        }
        catch (Exception e)
        {
            logger.info("{}", e);
        }
    }

    private void init_database()
    {
        Conf conf = new Conf();
        conf.setUser("dbuser");
        conf.setPassword("dbuser");
        conf.setHost("localhost");
        conf.setPort(3306);
        conf.setDatabase("data");
        Resource.setPersistenceProperties(PERSISTENCE_UNIT, "MYSQL",
                conf.getUser(), conf.getPassword(), conf.getHost(),
                conf.getPort(), conf.getDatabase());
    }

    private void close_database() throws Exception
    {
        Resource.getInstance().getEntityManagerHandler(PERSISTENCE_UNIT)
                .close();
    }

    private void create() throws Exception
    {
        logger.info("create");
        logger.info("");

        EntityManager em = null;

        try
        {
            em = Resource.getInstance()
                    .getEntityManagerHandler(PERSISTENCE_UNIT)
                    .createEntityManager();

            Clock clock = Clock.systemDefaultZone();
            long begin;
            long end;

            begin = clock.millis();
            int num = 10000;
            for (int i = 0; i < num; i++)
            {
                em.getTransaction().begin();

                em.createNativeQuery(String.format(
                        "create table if not exists test_create_%s("
                                + "id int not null auto_increment,"
                                + "name varchar(100) not null default '',"
                                + "primary key (id), key (name)"
                                + ") auto_increment=:auto_increment engine=innodb",
                        "201701")).setParameter("auto_increment", 1000)
                        .executeUpdate();

                em.getTransaction().commit();
            }
            end = clock.millis();
            System.out.println(
                    String.format("尝试创建表%d次，耗时%d毫秒", num, end - begin));
        }
        catch (Exception e)
        {
            logger.error("{}", e);
            try
            {
                if (em != null && em.getTransaction().isActive())
                    em.getTransaction().rollback();
            }
            catch (Exception er)
            {
                logger.error("rollback fail: {}", er);
            }
            throw e;
        }
        finally
        {
            if (em != null)
                em.close();
        }
    }

    private void add() throws Exception
    {
        Random rand = new Random();

        InputStream is = this.getClass().getResourceAsStream("/picture.jpg");
        byte[] picture = new byte[is.available()];
        logger.info("picture file length: {}", is.available());
        is.read(picture);
        is.close();

        EntityManager em = null;

        try
        {
            em = Resource.getInstance()
                    .getEntityManagerHandler(PERSISTENCE_UNIT)
                    .createEntityManager();

            for (int i = 1; i <= 5; i++)
            {
                try
                {
                    em.getTransaction().begin();

                    String login = String.format("JPA%03d@qq.com", i);
                    TestUser test_user;
                    try
                    {
                        test_user = (TestUser) em
                                .createQuery("from TestUser where login=:login")
                                .setParameter("login", login).getSingleResult();
                    }
                    catch (NoResultException e)
                    {
                        test_user = new TestUser();
                        test_user.setLogin(String.format("JPA%03d@qq.com", i));
                        test_user.setName(String.format("东风%03d", i));

                        TestJob job = em.find(TestJob.class, rand.nextInt(4));
                        // 也可以
                        // TestJob job=new TestJob();
                        // job.setId(2);
                        test_user.setJob(job);

                        test_user.setAddress("魔都");
                        test_user.setPicture(picture);

                        em.persist(test_user);

                        logger.info("new user(id:{} login:{})",
                                test_user.getId(), test_user.getLogin());
                    }

                    for (int j = 0; j < 3; j++)
                    {
                        TestLog test_log = new TestLog();
                        test_log.setTest_user(test_user);
                        test_log.setContent("如果多一次选择，你想变成谁？不，这不是选择，而是对自己的怀疑。"
                                + "我能经得住多大诋毁，就能担得起多少赞美。如果忍耐算是坚强，我选择抵抗。"
                                + "如果妥协算是努力，我选择争取。如果未来才会精彩，我也绝不放弃现在。"
                                + "你也许认为我疯狂，就像我认为你太过平常。" + "我的真实，会为我证明自己。"
                                + "诺基亚N9，不跟随。");
                        em.persist(test_log);
                    }

                    // 原生SQL，针对按规则命名的多张表
                    em.createNativeQuery("insert into test_log"
                            + " (id,create_time,user_id,content)"
                            + " values(:id,:create_time,:user_id,:content)")
                            .setParameter("id", 0)
                            .setParameter("create_time", new Date(),
                                    TemporalType.TIMESTAMP)
                            .setParameter("user_id", test_user.getId())
                            .setParameter("content", "诺基亚已被微软收购")
                            .executeUpdate();
                    long id = ((BigInteger) em
                            .createNativeQuery("select last_insert_id()")
                            .getSingleResult()).longValue();
                    logger.info("test_log: id({})", id);

                    em.getTransaction().commit();
                }
                catch (Exception e)
                {
                    logger.error("fail: {}", e);
                    try
                    {
                        if (em != null && em.getTransaction().isActive())
                            em.getTransaction().rollback();
                    }
                    catch (Exception er)
                    {
                        logger.error("rollback fail: {}", er);
                    }
                }
            }

            // 唯一约束违反
            try
            {

            }
            catch (ConstraintViolationException e)
            {

            }
            catch (Exception e)
            {

            }
        }
        finally
        {
            if (em != null)
                em.close();
        }
    }

    private void update() throws Exception
    {
        logger.info("update");
        logger.info("");

        EntityManager em = null;

        try
        {
            em = Resource.getInstance()
                    .getEntityManagerHandler(PERSISTENCE_UNIT)
                    .createEntityManager();

            em.getTransaction().begin();

            TestUser test_user = null;
            String login = "JPA003@qq.com";
            try
            {
                test_user = (TestUser) em
                        .createQuery("from TestUser where login=:login")
                        // .setLockMode(LockModeType.PESSIMISTIC_WRITE)
                        .setParameter("login", login).getSingleResult();
            }
            catch (NoResultException e)
            {
                logger.info("user(login:{}) not in test_user", login);
            }

            if (test_user != null)
            {
                // 悲观锁 == for update
                em.refresh(test_user, LockModeType.PESSIMISTIC_WRITE);
                test_user.setUpdate_time(new Date());
                test_user.setAddress("帝都" + Instant.now().toEpochMilli());
                // em.flush(); // 刷新到数据库
                logger.info("refresh user(id:{} login:{})", test_user.getId(),
                        test_user.getLogin());
            }

            em.getTransaction().commit();
        }
        catch (Exception e)
        {
            logger.error("{}", e);
            try
            {
                if (em != null && em.getTransaction().isActive())
                    em.getTransaction().rollback();
            }
            catch (Exception er)
            {
                logger.error("rollback fail: {}", er);
            }
        }
        finally
        {
            if (em != null)
                em.close();
        }
    }

    private void query() throws Exception
    {
        String split = "--------------------------------------------------";
        logger.info("query");
        logger.info("");

        EntityManager em = null;
        try
        {
            em = Resource.getInstance()
                    .getEntityManagerHandler(PERSISTENCE_UNIT)
                    .createEntityManager();

            List<?> rs;

            logger.info("单条不存在记录");
            logger.info("");
            String login_nonexist = "xxx";
            try
            {
                TestUser test_user = (TestUser) em
                        .createQuery("from TestUser u where u.login=:login")
                        .setParameter("login", login_nonexist)
                        .getSingleResult();
                logger.info("id:{} login:{} name:{}", test_user.getId(),
                        test_user.getLogin(), test_user.getName());
            }
            catch (NoResultException e)
            {
                logger.info("test_user(login:{}) not found, {}", login_nonexist,
                        e);
            }
            logger.info(split);

            logger.info("单表查询");
            logger.info("");
            rs = em.createQuery(
                    "from TestUser u where u.job.id>=:job" + " order by u.name")
                    .setParameter("job", 0).getResultList();
            for (int i = 0; i < rs.size(); i++)
            {
                TestUser test_user = (TestUser) rs.get(i);
                logger.info(
                        "id:{} login:{} name:{}"
                                + " job:({} {}) address:{} picture:{}",
                        test_user.getId(), test_user.getLogin(),
                        test_user.getName(), test_user.getJob().getId(),
                        test_user.getJob().getName(), test_user.getAddress(),
                        (test_user.getPicture() == null) ? 0
                                : test_user.getPicture().length);

                List<?> rs_log = em
                        .createQuery(
                                "from TestLog l where l.test_user.id=:user_id")
                        .setParameter("user_id", test_user.getId())
                        .getResultList();
                for (int j = 0; j < rs_log.size(); j++)
                {
                    TestLog test_log = (TestLog) rs_log.get(j);
                    logger.info("\tid:{} content:{}", test_log.getId(),
                            test_log.getContent());
                }
                logger.info("");
            }

            List<TestUser> test_user_list = ((List<?>) (em
                    .createQuery("from TestUser u where u.job.id>=:job"
                            + " order by u.name")
                    .setParameter("job", 0).getResultList())).stream()
                            .map(x -> (TestUser) x)
                            .collect(Collectors.toList());

            logger.info(split);

            logger.info("原生SQL");
            logger.info("");
            rs = em.createNativeQuery(
                    "select u.id,u.update_time,u.login,u.name,"
                            + "j.id as job_id,j.name as job_name,"
                            + "u.address,u.picture"
                            + " from test_user u, test_job j"
                            + " where u.job=j.id and u.job>=:job"
                            + " order by u.name")
                    .setParameter("job", 0).getResultList();
            for (int i = 0; i < rs.size(); i++)
            {
                Object[] row = (Object[]) rs.get(i);
                int col = 0;
                long id = ((BigInteger) row[col++]).longValue(); // bigint必须对应BigInteger
                Date update_time = (Date) row[col++];
                String login = (String) row[col++];
                String name = (String) row[col++];
                int job_id = ((Integer) row[col++]).intValue();
                String job_name = (String) row[col++];
                String address = (String) row[col++];
                byte[] picture = (byte[]) row[col++];
                logger.info(
                        "id:{} update_time:{} login:{} name:{}"
                                + " job:({} {}) address:{} picture:{}",
                        id, update_time, login, name, job_id, job_name, address,
                        (picture == null) ? 0 : picture.length);
            }

            ((List<?>) (em
                    .createNativeQuery(
                            "select u.id,u.update_time,u.login,u.name,"
                                    + "j.id as job_id,j.name as job_name,"
                                    + "u.address,u.picture"
                                    + " from test_user u, test_job j"
                                    + " where u.job=j.id and u.job>=:job"
                                    + " order by u.name")
                    .setParameter("job", 0).getResultList())).forEach(x -> {
                        Object[] row = (Object[]) x;
                    });

            logger.info(split);

            logger.info("隐示连接");
            logger.info("");
            rs = em.createQuery(
                    "select l.test_user.id, l.test_user.login, l.test_user.name,"
                            + " l.test_user.job, l.id" + " from TestLog l"
                            + " where l.test_user.job.id>=:job"
                            + " order by l.test_user.id")
                    .setParameter("job", 0).getResultList();
            for (int i = 0; i < rs.size(); i++)
            {
                Object[] row = (Object[]) rs.get(i);
                int col = 0;
                Long id = ((Long) row[col++]);
                String login = (String) row[col++];
                String name = (String) row[col++];
                TestJob test_job = (TestJob) row[col++];
                Long log_id = (Long) row[col++];
                logger.info("id:{} login:{} name:{}" + " job:({} {}) log_id:{}",
                        id, login, name, test_job.getId(), test_job.getName(),
                        (log_id == null) ? -1 : log_id);
            }
            logger.info(split);

            logger.info("显示连接");
            logger.info("");
            // 必须连接发起方在前
            rs = em.createQuery("select u.id, u.login, u.name, u.job, l.id"
                    + " from TestLog l right join l.test_user u"
                    + " where u.job.id>=:job" + " order by u.id")
                    .setParameter("job", 0).getResultList();
            for (int i = 0; i < rs.size(); i++)
            {
                Object[] row = (Object[]) rs.get(i);
                int col = 0;
                Long id = ((Long) row[col++]);
                String login = (String) row[col++];
                String name = (String) row[col++];
                TestJob test_job = (TestJob) row[col++];
                Long log_id = (Long) row[col++];
                logger.info("id:{} login:{} name:{}" + " job:({} {}) log_id:{}",
                        id, login, name, test_job.getId(), test_job.getName(),
                        (log_id == null) ? -1 : log_id);
            }
        }
        catch (Exception e)
        {
            logger.error("{}", e);
        }
        finally
        {
            if (em != null)
                em.close();
        }
    }

    private void remove() throws Exception
    {
        logger.info("remove");
        logger.info("");

        EntityManager em = null;

        try
        {
            em = Resource.getInstance()
                    .getEntityManagerHandler(PERSISTENCE_UNIT)
                    .createEntityManager();

            em.getTransaction().begin();

            String login = "JPA001@qq.com";
            List<?> rs = em
                    .createQuery("from TestLog l"
                            + " where l.test_user.login=:login")
                    .setParameter("login", login).getResultList();
            for (Object o : rs)
            {
                TestLog test_log = (TestLog) o;
                em.remove(test_log);
            }
            logger.info("remove test_log(login:{}) {} recs", login, rs.size());

            // em.createQuery(
            // "delete from TestLog l" + " where l.test_user.login=:login")
            // .setParameter("login", login).executeUpdate();

            em.getTransaction().commit();
        }
        catch (Exception e)
        {
            logger.error("{}", e);
            try
            {
                if (em != null && em.getTransaction().isActive())
                    em.getTransaction().rollback();
            }
            catch (Exception er)
            {
                logger.error("rollback fail: {}", er);
            }
        }
        finally
        {
            if (em != null)
                em.close();
        }
    }

    private void composite_primary_key() throws Exception
    {
        logger.info("composite_primary_key");
        logger.info("");

        EntityManager em = null;

        try
        {
            em = Resource.getInstance()
                    .getEntityManagerHandler(PERSISTENCE_UNIT)
                    .createEntityManager();

            em.getTransaction().begin();

            long user_id = 1;
            Date now = new Date();
            TestStatId test_stat_id = new TestStatId();
            test_stat_id.setStat_date(DateTimeFormatter.ofPattern("yyyyMMdd")
                    .format(LocalDateTime.now()));
            test_stat_id.setUser_id(user_id);
            TestStat test_stat = em.find(TestStat.class, test_stat_id);
            if (test_stat == null)
            {
                test_stat = new TestStat();
                test_stat.setStat_date(test_stat_id.getStat_date());
                test_stat.setUser_id(user_id);
                test_stat.setUpdate_ts(now);
                em.persist(test_stat);
                logger.info("new test_stat(stat_date:{} user_id:{})",
                        test_stat_id.getStat_date(), test_stat_id.getUser_id());
            }
            test_stat.setUpdate_ts(now);
            test_stat.setLog_count(test_stat.getLog_count() + 1);

            em.getTransaction().commit();
        }
        catch (Exception e)
        {
            logger.error("{}", e);
            try
            {
                if (em != null && em.getTransaction().isActive())
                    em.getTransaction().rollback();
            }
            catch (Exception er)
            {
                logger.error("rollback fail: {}", er);
            }
        }
        finally
        {
            if (em != null)
                em.close();
        }
    }

    private void multi_table() throws Exception
    {

    }
}
